
package com.joysuch.wwyt.risk.repository;

import java.util.List;

import javax.persistence.Tuple;

import com.joysuch.wwyt.risk.entity.RiskDistrict;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

/**
 * RiskDistrictDao
 */
public interface RiskDistrictDao extends JpaRepository<RiskDistrict, Long>, QuerydslPredicateExecutor<RiskDistrict> {
    public Page<RiskDistrict> findAll(Specification<RiskDistrict> spec, Pageable pageable);

    public RiskDistrict save(RiskDistrict bean);

    public void delete(RiskDistrict bean);


    //    @Query(value = "select count(id),riskLevel from RiskDistrict where riskLevel !=null group by riskLevel")
    @Query(value = " SELECT COUNT(*), real_time_risk_level as risk_level FROM wwyt_risk_district WHERE real_time_risk_level IS NOT NULL AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) GROUP BY real_time_risk_level", nativeQuery = true)
    List<Tuple> groupDistrictLevelCount();

    @Query(value = " SELECT COUNT(*), dynamic_risk_level as risk_level FROM wwyt_risk_district WHERE own_level IS NOT NULL AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) GROUP BY dynamic_risk_level", nativeQuery = true)
    List<Tuple> groupDistrictDynamicLevelCount();

    @Query(value = " SELECT * FROM wwyt_risk_district  WHERE CODE =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public List<RiskDistrict> findByCode(String code);

    @Query(value = " SELECT * FROM wwyt_risk_district  WHERE NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public RiskDistrict findFirstByName(String name);

    @Query(value = "select name from RiskDistrict where id = ?1")
    String findNameById(Long id);

    @Query(value = "select name from RiskDistrict where id = ?1")
    public String getNameById(Long riskDistrictId);

    // @Query("select count(m) from RiskDistrict m where name = ?1")
    @Query(value = " SELECT count(1) FROM wwyt_risk_district WHERE NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    long countOfName(String name);

    // @Query("select count(m) from RiskDistrict m where name = ?1 and id != ?2")
    @Query(value = " SELECT count(1) FROM wwyt_risk_district WHERE NAME =?1 AND ID !=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    long countOfNameExcludeId(String name, Long id);

    // @Query(value = "SELECT id FROM `wwyt_risk_district` WHERE NAME =?1", nativeQuery = true)
    @Query(value = " SELECT ID FROM wwyt_risk_district WHERE NAME =?1  AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByName(String riskDistrictName);

    @Query(value = "SELECT last_evaluation_id FROM `wwyt_risk_district` WHERE id =?1", nativeQuery = true)
    Long getLastEvaluationId(Long id);

    @Query(value = "SELECT d.* FROM `wwyt_risk_district` d WHERE d.DELETE_FLAG ='0' AND (d.state IS NULL OR d.state !=44)", nativeQuery = true)
    List<RiskDistrict> findAll();

    //@Query(value = "SELECT * FROM `wwyt_risk_district` WHERE code =?1 and org_code like ?2%  limit 1 ", nativeQuery = true)
    @Query(value = " SELECT * FROM wwyt_risk_district WHERE CODE =?1 AND ORG_CODE LIKE ?2% AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) LIMIT 1", nativeQuery = true)
    public RiskDistrict findByCodeAndCompanyCode(String code, String companyCode);

    @Query(value = " SELECT * FROM wwyt_risk_district WHERE CODE LIKE %:districtNameOrCode% OR NAME LIKE %:districtNameOrCode% AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) LIMIT 1", nativeQuery = true)
    List<RiskDistrict> getListByNameOrCode(@Param("districtNameOrCode") String districtNameOrCode);

    @Query(value = " SELECT * FROM wwyt_risk_district WHERE  DELETE_FLAG ='0' AND (state IS NULL OR state !=44) and if(:districtName !='',NAME like %:districtName%,1=1) and if(:riskLevel != '' ,risk_level = :riskLevel,1=1)", nativeQuery = true)
    List<RiskDistrict> getDistrictByCondition(@Param("districtName") String districtName, @Param("riskLevel") Integer riskLevel);

    @Query(value = " SELECT * FROM wwyt_risk_district  WHERE NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    List<RiskDistrict> findByName(String name);

    @Query(value = "SELECT d.* FROM wwyt_risk_point p INNER JOIN wwyt_risk_district d ON p.RISK_DISTRICT_ID = d.id  WHERE  p.CODE = ?1 AND p.delete_flag = '0' AND d.delete_flag ='0' AND ( d.state IS NULL OR d.state != 44)", nativeQuery = true)
    RiskDistrict getByPointCode(String code);

    @Query(value = " SELECT count(1) FROM wwyt_risk_district WHERE CODE =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfCode(String code);

    @Query(value = " SELECT count(1) FROM wwyt_risk_district WHERE CODE =?1 AND ID !=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfCodeExcludeId(String code, Long id);

    @Query(value = "select district.coordinates " +
            "from wwyt_risk_point point " +
            "         left join wwyt_risk_district district on point.RISK_DISTRICT_ID = district.ID " +
            "where point.ID = ?1", nativeQuery = true)
    String getLocationByPointId(Long pointId);

    @Query(value = " select * " +
            " from WWYT_RISK_DISTRICT " +
            " where COORDINATES is not null " +
            "  and DELETE_FLAG = '0' " +
            "  and (state != '44' or state is null) ", nativeQuery = true)
    List<RiskDistrict> findAllHaveLocation();

    @Query(value = "select coordinates from wwyt_risk_district where id = ?1", nativeQuery = true)
    String findLocationById(Long id);

    @Query(value = "SELECT * FROM `wwyt_risk_district`  WHERE org_code LIKE ?1% AND tenent_id =?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<RiskDistrict> findAllByOrgCodeAndTenentId(String orgCode, Long tenentId);

    @Query(value = " SELECT count(1) FROM wwyt_risk_district WHERE evaluation_state = 1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long getDistEvalNum();

    @Query(value = "select count(id) from wwyt_risk_district where responsible_dept = ?1 and DELETE_FLAG = '0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByDepartId(Long id);

//    @Query(value = " SELECT COUNT(*), stable_risk_level as risk_level FROM wwyt_risk_district WHERE stable_risk_level IS NOT NULL AND DELETE_FLAG ='0' AND org_code LIKE ?1% AND  tenent_id =?2 AND (state IS NULL OR state !=44) GROUP BY real_time_risk_level", nativeQuery = true)
//    List<Tuple> groupDistrictRealLevelCountByOrgCodeAndTenentId(String orgCode, Long tenentId);
//
//    @Query(value = " SELECT COUNT(*), real_time_risk_level as risk_level FROM wwyt_risk_district WHERE real_time_risk_level IS NOT NULL AND DELETE_FLAG ='0'AND org_code LIKE ?1% AND  tenent_id =?2 AND (state IS NULL OR state !=44) GROUP BY dynamic_risk_level", nativeQuery = true)
//    List<Tuple> groupDistrictDynamicLevelCountByOrgCodeAndTenentId(String orgCode, Long tenentId);
}
